package com.dao;

import com.entity.User;
import com.utils.DBUtilsHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class BaseDao {

//	ArrayHandler：把结果集中的第一行数据转成对象数组。
//	ArrayListHandler：把结果集中的每一行数据都转成一个对象数组，再存放到List中。
//	BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
//	BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
//	ColumnListHandler：将结果集中某一列的数据存放到List中。
//	KeyedHandler：将结果集中的每一行数据都封装到一个Map里，然后再根据指定的key把每个Map再存放到一个Map里。
//	MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
//	MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List。
//	ScalarHandler：将结果集中某一条记录的其中某一列的数据存成Object。
		
	public static QueryRunner runner = null;
	static {
		runner = new DBUtilsHelper().getRunner();
	}

	//获取数据源连接
	public Connection getConnection() throws SQLException {
		return runner.getDataSource().getConnection();
	}

	//关闭数据源连接
	public void CloseConnection() throws SQLException {
		runner.getDataSource().getConnection().close();
	}

	public <T> T findBean(Class<T> beanClass, String sql, Object... params) {
		try {
			return runner.query(sql, new BeanHandler<T>(beanClass), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public <T> T findBean(Class<T> beanClass, String sql) {
		try {
			return runner.query(sql, new BeanHandler<T>(beanClass));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public <T> List<T> findList(Class<T> beanClass, String sql, Object... params) {
		try {
			return runner.query(sql, new BeanListHandler<T>(beanClass), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public <T> List<T> findList(Class<T> beanClass, String sql) {
		try {
			return runner.query(sql, new BeanListHandler<T>(beanClass));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public long getCount(String sql, Object... params) {
		try {
			return (long) runner.query(sql, new ScalarHandler<Long>(), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public int count(String sql) {
		try {
			Long l = runner.query(sql, new ScalarHandler<>());
			return Integer.parseInt(l.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	public int count(String sql,String catpost) {
		try {
			Long l = runner.query(sql, new ScalarHandler<>(),catpost);
			return Integer.parseInt(l.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public int update(String sql, Object... params) {
		try {
			return runner.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public int[] batch(String sql, Object[][] params) {
		try {
			return runner.batch(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Object query(Object obj) throws Exception {
		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "select * from "+tableName +" where id = ?";
		return findBean(clas, sql, clas.getDeclaredMethod("getId").invoke(obj));
	}

	public Object queryKnowledge(Object obj) throws Exception {
		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "select * from "+tableName +" where title = ?";
		return findBean(clas, sql, clas.getDeclaredMethod("getTitle").invoke(obj));
	}

	public Object queryVarieties(Object obj) throws Exception {
		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "select * from "+tableName +" where Varieties = ?";
		return findBean(clas, sql, clas.getDeclaredMethod("getVarieties").invoke(obj));
	}

	public int delete(Object obj) throws Exception {
		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "delete from "+tableName +" where id = ?";

		return update(sql, clas.getDeclaredMethod("getId").invoke(obj));
	}
	public int deleteVarietiess(Object obj) throws Exception {
		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "delete from "+tableName +" where Varieties = ?";

		return update(sql, clas.getDeclaredMethod("getVarieties").invoke(obj));
	}

	public int deleteCatPost(Object obj) throws Exception {
		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "delete from "+tableName +" where id = ?";

		return update(sql, clas.getDeclaredMethod("getId").invoke(obj));
	}

	public int deleteVarieties(Object obj) throws Exception {
		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "delete from "+tableName +" where varieties = ?";

		return update(sql, clas.getDeclaredMethod("getVarieties").invoke(obj));
	}

	/**
	 * 所有的表的插入都可以用这个方法
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public int insert(Object obj) throws Exception {
		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "INSERT INTO "+tableName;
		//参数部分
		String sqlParams = "(";
		//占位符部分
		String sqlZ = "(";
		//循环所有的字段
		Field[] fields = clas.getDeclaredFields();
		//参数
		Object[] params = new Object[fields.length];
		int i = 0;
		for (Field field : fields) {
			String name = field.getName();
			sqlParams += name+",";
			sqlZ += "?,";
			//调用get方法
			//获取方法

			String getMethodName = "get" + name.substring(0,1).toUpperCase() + name.substring(1);
			//如果id有值，则直接复制，否则赋值为空值，让数据库自增。
			if (name.equals("id")) {
				Object idValue = clas.getDeclaredMethod(getMethodName).invoke(obj);
				if (idValue.equals(0))
					params[i] = null;
				else
					params[i] = idValue;
				i++;
				continue;
			}
			params[i] = clas.getDeclaredMethod(getMethodName).invoke(obj);
			i++;
		}
		//去掉最后一个逗号
		sqlParams = sqlParams.substring(0, sqlParams.length()-1);

		sqlZ = sqlZ.substring(0, sqlZ.length()-1);
		sqlParams += ")";
		sqlZ += ")";

		sql = sql + sqlParams + " values " + sqlZ;
		System.out.println(sql);
		return update(sql, params);
	}

	/**
	 * 所有的表的更新都可以用这个方法
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public int update(Object obj) throws Exception {

		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "UPDATE " + tableName +" SET ";
		//参数部分
		String sqlParams = "";
		//循环所有的字段
		Field[] fields = clas.getDeclaredFields();
		//参数
		Object[] params = new Object[fields.length];
		int i = 0;
		for (Field field : fields) {
			String name = field.getName();
			if (name.equals("id"))
				continue;
			sqlParams += name+"=?,";
			//调用get方法
			//获取方法
			String getMethodName = "get" + name.substring(0,1).toUpperCase() + name.substring(1);
			params[i] = clas.getDeclaredMethod(getMethodName).invoke(obj);
			i++;
		}
		//去掉最后一个逗号
		sqlParams = sqlParams.substring(0, sqlParams.length()-1);

		sql = sql + sqlParams + " where id = ?";System.out.println(sql);
		//最后一个位置放入id
		params[params.length-1] = clas.getDeclaredMethod("getId").invoke(obj);

		return update(sql, params);
	}

	public int updateVarieties(Object obj) throws Exception {

		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "UPDATE " + tableName +" SET ";
		//参数部分
		String sqlParams = "";
		//循环所有的字段
		Field[] fields = clas.getDeclaredFields();
		//参数
		Object[] params = new Object[fields.length];
		int i = 0;
		for (Field field : fields) {
			String name = field.getName();
			if (name.equals("varieties"))
				continue;
			sqlParams += name+"=?,";
			//调用get方法
			//获取方法
			String getMethodName = "get" + name.substring(0,1).toUpperCase() + name.substring(1);
			params[i] = clas.getDeclaredMethod(getMethodName).invoke(obj);
			i++;
		}
		//去掉最后一个逗号
		sqlParams = sqlParams.substring(0, sqlParams.length()-1);

		sql = sql + sqlParams + " where varieties = ?";
		//最后一个位置放入id
		params[params.length-1] = clas.getDeclaredMethod("getVarieties").invoke(obj);

		return update(sql, params);
	}

	public int updateKnowledges(Object obj) throws Exception {

		//1.获取对象的反射
		Class clas = obj.getClass();
		//获取类名
		String tableName = clas.getSimpleName();
		//2.拼接sql
		String sql = "UPDATE " + tableName +" SET ";
		//参数部分
		String sqlParams = "";
		//循环所有的字段
		Field[] fields = clas.getDeclaredFields();
		//参数
		Object[] params = new Object[fields.length];
		int i = 0;
		for (Field field : fields) {
			String name = field.getName();
			if (name.equals("title"))
				continue;
			sqlParams += name+"=?,";
			//调用get方法
			//获取方法
			String getMethodName = "get" + name.substring(0,1).toUpperCase() + name.substring(1);
			params[i] = clas.getDeclaredMethod(getMethodName).invoke(obj);
			i++;
		}
		//去掉最后一个逗号
		sqlParams = sqlParams.substring(0, sqlParams.length()-1);

		sql = sql + sqlParams + " where title = ?";
		//最后一个位置放入id
		params[params.length-1] = clas.getDeclaredMethod("getTitle").invoke(obj);

		return update(sql, params);
	}

}
